package com.example.dao;

import com.example.bean.Product;
import com.example.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品
 */

public class ProductDao {
    ResultSet resultSet = null;
    Connection connection = null;
    Statement statement = null;
    PreparedStatement preparedStatement = null;

    /**
     * 查询前十二个热门商品的id,图片路径,商城售价,商品名称
     */
    public List<Product> selectHotProduct() {
        List<Product> hotProcuctList = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from product where is_hot = 1 limit 12");
            while (resultSet.next()) {
                Product product = new Product();
                product.setId(resultSet.getInt("pid"));
                product.setName(resultSet.getString("pname"));
                product.setShop_price(resultSet.getDouble("shop_price"));
                product.setImage(resultSet.getString("pimage"));
                hotProcuctList.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, statement, connection);
        }
        return hotProcuctList;
    }

    /**
     * 查询所有商品并且分页
     */
    public List<Product> selectAllProductByPage(int pageNo, int pageSize) {
        List<Product> plist = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement("SELECT * FROM product LIMIT ?, ?");
            preparedStatement.setInt(1, (pageNo - 1) * pageSize);
            preparedStatement.setInt(2, pageSize);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Product product = new Product();
                product.setId(resultSet.getInt("pid"));
                product.setName(resultSet.getString("pname"));
                product.setShop_price(resultSet.getDouble("shop_price"));
                product.setImage(resultSet.getString("pimage"));
                plist.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, statement, connection);
        }
        System.out.println("Product list size: " + plist.size()); // 调试输出
        return plist;  // 返回商品列表
    }

    /**
     * 查询所有商品的总记录数
     */
    public int selectAllProductCount() {
        int count = 0;
        try {
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT COUNT(*) FROM product");
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, statement, connection);
        }
        return count;
    }

    /**
     * 模糊查询
     */
    public List<Product> searchProductsByQuery(String query, int pageNo, int pageSize) {
        List<Product> plist = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();

            // 判断查询条件是否是数字（价格）
            if (isNumeric(query)) {
                // 查询价格的产品
                preparedStatement = connection.prepareStatement("SELECT * FROM product WHERE shop_price = ? LIMIT ?, ?");
                preparedStatement.setDouble(1, Double.parseDouble(query));
                preparedStatement.setInt(2, (pageNo - 1) * pageSize);  // 设置偏移量
                preparedStatement.setInt(3, pageSize);  // 设置每页记录数
            } else {
                // 查询名称的产品
                preparedStatement = connection.prepareStatement("SELECT * FROM product WHERE pname LIKE ? LIMIT ?, ?");
                preparedStatement.setString(1, "%" + query + "%");
                preparedStatement.setInt(2, (pageNo - 1) * pageSize);  // 设置偏移量
                preparedStatement.setInt(3, pageSize);  // 设置每页记录数
            }

            resultSet = preparedStatement.executeQuery();

            // 处理查询结果
            while (resultSet.next()) {
                Product product = new Product();
                product.setId(resultSet.getInt("pid"));
                product.setName(resultSet.getString("pname"));
                product.setShop_price(resultSet.getDouble("shop_price"));
                product.setImage(resultSet.getString("pimage"));
                plist.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, preparedStatement, connection);
        }
        return plist;  // 返回商品列表
    }

    /**
     * 判断字符串是否为数字
     */
    private boolean isNumeric(String str) {
        if (str == null || str.isEmpty()) {
            return false;
        }
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }


    /**
     * 模糊查询的总记录数
     */
    public int selectProductCountByQuery(String query) {
        int count = 0;
        try {
            connection = DBUtil.getConnection();

            // 判断查询条件是否是数字（价格）
            if (isNumeric(query)) {
                // 查询价格的产品总数
                preparedStatement = connection.prepareStatement("SELECT COUNT(*) FROM product WHERE shop_price = ?");
                preparedStatement.setDouble(1, Double.parseDouble(query));
            } else {
                // 查询名称的产品总数
                preparedStatement = connection.prepareStatement("SELECT COUNT(*) FROM product WHERE pname LIKE ?");
                preparedStatement.setString(1, "%" + query + "%");
            }

            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                count = resultSet.getInt(1);  // 获取查询到的总记录数
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, preparedStatement, connection);
        }

        return count;  // 返回商品总数
    }

    /**
     * 根据ID查询商品详情
     */
    public Product selectProductInfoById(int id) {
        Product product = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement("select * from product where pid = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                product = new Product();
                product.setId(resultSet.getInt("pid"));
                product.setName(resultSet.getString("pname"));
                product.setShop_price(resultSet.getDouble("shop_price"));
                product.setMarket_price(resultSet.getDouble("market_price"));
                product.setImage(resultSet.getString("pimage"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, statement, connection);
        }
        return product;
    }

}
